<?php

/* * ***********************************************************************
  nom           :   Database Library
  version       :   1.0.93
  autheur       :   pma
  date          :   01/12/2012
  description   :   Cette librairie contient toutes les fonctions et tous les paramètres
  pour la gestion de la communication entre la couche applicative et
  la base de données. Les requetes sont classées par modules:
  -   de 0 à 99 les requetes du module administration
  -   de 100 à 199 les requetes du module EAI
 * *********************************************************************** */

//============================================================================
// DATABASE PARAMETERS 
//============================================================================
define('DB_HOST', 'localhost');
define('DB_PORT', 3306);
define('DB_NAME', 'sirh-central');
define('DB_USER', 'sirh-central');
define('DB_PWD', 'motdepasse');

//============================================================================
// SCRIPTS
//============================================================================
class Queries {

    static function ADM() {
        return PATH_SQL . 'adm.sql';
    }

    static function EAI() {
        return PATH_SQL . 'eai.sql';
    }

    static function EAI_TEMPLATE($index) {
        $filename = PATH_SQL . "sirh_modele_$index.sql";
        if (file_exists($filename)) {
            return $filename;
        }
    }

    const REQUEST_061 = <<<SQL
SELECT
    A.nom,
    A.nom_epouse,
    A.prenom, 
    A.matricule, 
    A.email, 
    A.fonction, 
    B.designation, 
    DATE_FORMAT(A.date_entree_societe, '%d/%m/%Y'),
    DATE_FORMAT(A.date_sortie_societe, '%d/%m/%Y'), 
    NULL,
    NULL,
    C.designation,
    TRIM(CONCAT(D.nom,' ',D.prenom))
FROM adm_utilisateur as A
INNER JOIN adm_client_arborescence as B ON B.id_noeud=A.id_noeud
INNER JOIN adm_profil as C ON C.id_profil=A.id_profil
LEFT JOIN adm_utilisateur AS D ON D.id_user=A.id_manager
SQL;

}

//============================================================================
// REQUESTS ADM
//============================================================================
$REQUEST_001 = " SELECT cnx.id_user, cnx.id_compte_client FROM adm_connexion as cnx WHERE login=:login AND pwd=:pwd ";
$REQUEST_002 = " SELECT 	uti.id_user,
							uti.nom,  
							uti.prenom, 
							uti.nom_epouse,
							uti.matricule, 
							uti.email, 
							uti.id_manager, 
							uti.id_noeud, 
							cab.designation, 
							cab.type, 
							uti.fonction, 
							DATE_FORMAT(uti.date_entree_societe, '%d/%m/%Y'),
							DATE_FORMAT(uti.date_sortie_societe, '%d/%m/%Y'), 
							uti.id_profil, 
							prf.designation,
							prf.id_profil_type
					FROM adm_utilisateur as uti  
					LEFT OUTER JOIN adm_client_arborescence as cab ON uti.id_noeud=cab.id_noeud
					LEFT OUTER JOIN adm_profil as prf ON uti.id_profil=prf.id_profil
					WHERE uti.id_user= :param_id_user ";
$REQUEST_004 = "	SELECT count(*) as nb FROM adm_compte_client WHERE nom = :client_name ";
$REQUEST_005 = "	SELECT count(*) as nb FROM adm_compte_client WHERE db_name = :db_name ";
$REQUEST_006 = "	INSERT INTO adm_compte_client (nom, db_name, eai_statut,eai_date_creation,eai_date_debut_contrat,eai_date_fin_contrat, option_gui_client) VALUES (:client_name, :db_name , :eai, NOW(), :start_eai , :end_eai , :opt_graph )";
$REQUEST_007 = "	DELETE FROM adm_compte_client WHERE id_compte_client=:id_client_account";
$REQUEST_003 = "	SELECT id_compte_client, nom, db_name, eai_statut, DATE_FORMAT(eai_date_creation, '%d/%m/%Y'), DATE_FORMAT(eai_date_debut_contrat, '%d/%m/%Y'), DATE_FORMAT(eai_date_fin_contrat, '%d/%m/%Y'), option_gui_client FROM adm_compte_client WHERE id_compte_client= :param_id_compte_client ";
$REQUEST_008 = "	SELECT id_compte_client, nom, db_name, eai_statut, DATE_FORMAT(eai_date_creation, '%d/%m/%Y'), DATE_FORMAT(eai_date_debut_contrat, '%d/%m/%Y'), DATE_FORMAT(eai_date_fin_contrat, '%d/%m/%Y'), option_gui_client FROM adm_compte_client ORDER BY nom";
$REQUEST_009 = "	SELECT db_name FROM adm_compte_client WHERE id_compte_client= :param_id_compte_client ";
//$REQUEST_010 = "	SELECT count(*) as nb FROM adm_compte_client ";
$REQUEST_011 = "	UPDATE adm_compte_client SET nom=:client_name, eai_statut=:eai, eai_date_debut_contrat=:start_eai, eai_date_fin_contrat=:end_eai, option_gui_client=:opt_graph WHERE id_compte_client=:id_client_account   ";
$REQUEST_012 = "	SELECT id_profil, designation, id_profil_type FROM adm_profil ORDER BY id_profil ASC, id_profil_type ASC";
$REQUEST_013 = "	UPDATE adm_profil SET designation=:designation, id_profil_type=:id_profil_type WHERE id_profil=:id_profil ";
$REQUEST_014 = "	DELETE FROM adm_profil WHERE id_profil=:id_profil ";
$REQUEST_015 = "	INSERT INTO adm_profil (designation,id_profil_type) VALUES (:designation,:id_profil_type) ";
$REQUEST_016 = "	SELECT count(*) as nb FROM adm_profil WHERE designation = :designation AND id_profil <> :id_profil ";
$REQUEST_017 = " SELECT uti.matricule, uti.nom, uti.nom_epouse, uti.prenom, uti.email, uti.id_manager,uti.id_noeud, cab.designation, cab.type, uti.fonction, uti.date_entree_societe, uti.date_sortie_societe, uti.id_profil, prf.designation FROM adm_utilisateur as uti, adm_client_arborescence as cab, adm_profil as prf WHERE uti.id_user= :param_id_user AND uti.id_noeud=cab.id_noeud AND uti.id_profil=prf.id_profil ";
$REQUEST_018 = " SELECT uti.id_user, uti.nom, uti.prenom, prf.designation FROM adm_utilisateur as uti, adm_profil as prf WHERE ( prf.id_profil_type = 2 OR prf.id_profil_type = 3 OR prf.id_profil_type = 4 OR prf.id_profil_type = 5 )  AND uti.id_profil=prf.id_profil";
$REQUEST_019 = " SELECT aca.id_noeud, aca.designation, aca.type, aca.id_parent FROM adm_client_arborescence as aca ORDER BY id_parent ASC";
$REQUEST_020 = " SELECT type_noeud, designation FROM adm_type_noeud ORDER BY type_noeud ASC";
$REQUEST_021 = " SELECT id_noeud, designation FROM adm_client_arborescence WHERE type=40 ";
$REQUEST_022 = " INSERT INTO adm_client_arborescence(designation, type, id_parent) VALUES (:designation,:type,:noeud_parent) ";
$REQUEST_023 = " UPDATE adm_client_arborescence SET designation=:designation,type=:type WHERE id_noeud=:id_noeud ";
$REQUEST_024 = " DELETE FROM adm_client_arborescence WHERE id_noeud=:id_noeud ";
$REQUEST_025 = " INSERT INTO adm_utilisateur ( id_user, matricule, nom,nom_epouse,prenom,email,id_manager,id_noeud,fonction,date_entree_societe,date_sortie_societe,id_profil) VALUES (:id_user, :user_matricule,:user_name,:user_spousename,:user_firstname,:user_email,:user_manager_name,:user_node,:user_function,:user_date_start_contract,:user_date_end_contract,:user_profile)";
$REQUEST_026 = " SELECT uti.id_user,CONCAT(uti.nom,' ',uti.prenom) FROM adm_utilisateur as uti ";
$REQUEST_027 = " SELECT uti.id_user,CONCAT(uti.nom,' ',uti.prenom) as nom FROM adm_utilisateur as uti  WHERE uti.id_manager= :id_user ";
$REQUEST_028 = " SELECT aca.id_noeud,  CONCAT(atn.designation,' - ',aca.designation) FROM adm_client_arborescence as aca, adm_type_noeud as atn WHERE aca.type=atn.type_noeud ORDER BY id_parent ASC";
$REQUEST_029 = " SELECT cnx.login, cnx.pwd, cnx.id_compte_client FROM adm_connexion as cnx WHERE cnx.id_user=:id_user ";
$REQUEST_030 = " SELECT id_compte_client, nom, db_name FROM adm_compte_client ";
$REQUEST_031 = " SELECT id_profil, designation , id_profil_type FROM adm_profil WHERE id_profil_type<>5 ORDER BY id_profil ASC, id_profil_type ASC";
$REQUEST_032 = " INSERT INTO adm_connexion( login, pwd, id_compte_client) VALUES (:login,:pwd,:id_client_account)";
//$REQUEST_033 = " SELECT cnx.id_user FROM adm_connexion as cnx WHERE login=:login AND pwd=:pwd AND cnx.id_compte_client=:id_client_account";
$REQUEST_034 = " SELECT id_compte_client, nom, db_name, eai_statut, DATE_FORMAT(eai_date_creation, '%d/%m/%Y'), DATE_FORMAT(eai_date_debut_contrat,'%d/%m/%Y'), DATE_FORMAT(eai_date_fin_contrat,'%d/%m/%Y'), option_gui_client FROM adm_compte_client ";
$REQUEST_035 = " UPDATE adm_connexion SET login=:login, pwd=:pwd  WHERE id_user=:id_user ";
$REQUEST_036 = " UPDATE adm_utilisateur SET matricule=:user_matricule,nom_epouse=:user_spousename, prenom=:user_firstname, email=:user_email, id_manager=:user_id_manager,id_noeud=:user_node,fonction=:user_function, date_entree_societe=:user_date_start_contract,date_sortie_societe=:user_date_end_contract,id_profil=:user_profile WHERE  id_user=:id_user";
$REQUEST_037 = " SELECT cnx.id_user FROM adm_connexion as cnx WHERE login=:login ";
$REQUEST_038 = " SELECT cnx.id_user FROM adm_connexion as cnx WHERE login=:login AND cnx.id_user <> :id_user";
$REQUEST_039 = " DELETE FROM adm_utilisateur WHERE id_user=:id_user";
$REQUEST_040 = " SELECT id_template, designation , name  FROM adm_eai_template ";
//$REQUEST_041 = " SELECT count(*) as nb FROM adm_client_arborescence ";
$REQUEST_042 = " SELECT  aca.id_noeud, 
							aca.designation , 
							atn.type_noeud, 
							atn.designation , 
							aca.id_parent, 
							(   SELECT CONCAT(atn2.designation,' - ',aca2.designation) 
								FROM    adm_client_arborescence as aca2 , 
										adm_type_noeud as atn2 
								WHERE   aca.id_parent=aca2.id_noeud 
								AND     aca2.type=atn2.type_noeud ) 
					FROM    adm_client_arborescence as aca, adm_type_noeud as atn
					WHERE   aca.id_noeud=:id_noeud
					AND     aca.type=atn.type_noeud";
$REQUEST_043 = " SELECT id_user, matricule, nom, prenom FROM adm_utilisateur WHERE id_noeud=:id_noeud";
$REQUEST_044 = " SELECT id_noeud as nb FROM adm_client_arborescence WHERE id_parent=:id_noeud";
$REQUEST_045 = " DELETE FROM adm_connexion WHERE id_user=:id_user";
$REQUEST_046 = " SELECT id_profil_type, designation FROM adm_profil_type ORDER BY id_profil_type ASC";
$REQUEST_047 = " SELECT id_profil_type, designation FROM adm_profil_type WHERE id_profil_type<>5 ORDER BY id_profil_type ASC";
$REQUEST_048 = " SELECT count(*) as nb FROM adm_utilisateur WHERE id_profil=:id_profil";
$REQUEST_049 = " DELETE FROM adm_connexion WHERE id_compte_client =:id_client_account";
$REQUEST_050 = " SELECT uti.id_user, CONCAT(uti.prenom, ' ',uti.nom) , uti.fonction, prf.designation, 
					case 
                                when arb.type=10 then CONCAT('Société : ',arb.designation)
                                when arb.type=20 then CONCAT('Établissement : ',arb.designation)
                                when arb.type=30 then CONCAT('Département : ',arb.designation)
                                when arb.type=30 then CONCAT('Service : ',arb.designation)
                            END AS design
					FROM adm_utilisateur as uti, adm_profil as prf , adm_client_arborescence as arb 
					WHERE ( prf.id_profil_type = 2  OR prf.id_profil_type = 3 OR prf.id_profil_type = 4 OR prf.id_profil_type = 5 )  
					AND uti.id_profil=prf.id_profil
					AND arb.id_noeud=uti.id_noeud"; // A CODER : les type de noeud devraient faire l'object d'un table de données pour ne pas bloquer le multi langue (si un jour cela est demandé)
$REQUEST_051 = " SELECT uti.id_user, uti.nom, uti.prenom, prf.designation, uti.id_manager, prf.id_profil_type FROM adm_utilisateur as uti, adm_profil as prf WHERE uti.id_manager=:id_manager AND ( prf.id_profil_type = 2  OR prf.id_profil_type = 3 OR prf.id_profil_type = 4 OR prf.id_profil_type = 5 )  AND uti.id_profil=prf.id_profil";
$REQUEST_052 = " SELECT id_profil_type FROM adm_utilisateur as uti, adm_profil as pro WHERE uti.id_user=:id_user AND uti.id_profil=pro.id_profil ";
$REQUEST_053 = " SELECT uti.id_user, uti.nom, uti.prenom, prf.designation, uti.email FROM adm_utilisateur as uti, adm_profil as prf WHERE ( prf.id_profil_type = 2 OR prf.id_profil_type = 3 OR prf.id_profil_type = 4 OR prf.id_profil_type = 5 )  AND uti.id_profil=prf.id_profil AND uti.nom=:manager_nom AND uti.prenom=:manager_prenom";
//$REQUEST_054 = " SELECT id_profil, designation FROM adm_profil WHERE id_profil_type IN (SELECT id_profil_type FROM adm_profil_type WHERE UPPER(designation) = UPPER(:designationProfil) ORDER BY id_profil_type ASC)";
//$REQUEST_055 = " SELECT uti.id_user,uti.nom,  uti.prenom FROM adm_utilisateur as uti WHERE UPPER(uti.nom)=UPPER(:nom) AND UPPER(uti.prenom)=UPPER(:prenom)";
//$REQUEST_056 = " SELECT aca.id_noeud FROM adm_client_arborescence as aca WHERE UPPER(aca.designation)=UPPER(:designation)";
$REQUEST_057 = " SELECT uti.id_manager, uti.nom,  uti.prenom FROM adm_utilisateur as uti  WHERE  uti.id_user= :id_user ";
$REQUEST_058 = " ";
$REQUEST_059 = " ";
$REQUEST_060 = " ";

$REQUEST_061 = <<<SQL
    SELECT A.id_user, B.id_profil_type 
    FROM adm_utilisateur AS A
    INNER JOIN adm_profil AS B
    ON A.id_profil=B.id_profil
    WHERE CONCAT(nom,' ',prenom)=:manager_name_and_firstname
SQL;

//============================================================================
// REQUESTS EAI - a voir si on éclate plus tard pour mettre cette partie dans le module EAI...plus logique
//============================================================================
$REQUEST_100 = " SELECT 	uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%Y'),
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							uti.matricule,
							cab.designation,
							uti.fonction,
							prf.designation,
							uti2.nom,  
							uti2.prenom,
							uti3.nom,  
							uti3.prenom
					FROM 	eai_appraisal as app, adm_utilisateur as uti , adm_utilisateur as uti2 , adm_utilisateur as uti3 ,adm_client_arborescence as cab, adm_profil as prf  
					WHERE 	app.id_eai=:id_eai 
					AND 	app.id_user=uti.id_user
					AND 	app.id_manager=uti2.id_user
					AND 	app.id_manager_interview=uti3.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil ";
$REQUEST_101 = " SELECT app.id_eai, DATE_FORMAT(app.date,'%Y'),DATE_FORMAT(app.date, '%d/%m/%Y') FROM eai_appraisal as app WHERE app.id_user=:id_user AND app.id_status=:id_status ORDER BY app.date DESC ";
$REQUEST_102 = " SELECT tmp.name FROM eai_appraisal as app, eai_template as tmp WHERE app.id_eai=:id_eai AND app.id_template=tmp.id_template ";
$REQUEST_103 = " SELECT sta.id_stage, sta.rank, sta.name, sta.activate FROM eai_stage as sta, eai_template as tmp WHERE tmp.name=:eai_template_name AND tmp.id_template=sta.id_template ORDER BY sta.rank ASC"; //récupère des étapes d'un modèle EAI
$REQUEST_104 = " SELECT ead.label, DATE_FORMAT(ead.grade, '%d/%m/%Y'), ead.comment 
					FROM eai_appraisal_data as ead, eai_criterion as cri 
					WHERE ead.id_eai=:id_eai 
					AND cri.id_theme=:id_theme 
					AND cri.id_criterion=ead.id_criterion 
					ORDER BY cri.rank ASC";
$REQUEST_105 = " SELECT the.id_theme, the.name, the.rank, the.activate FROM eai_theme as the WHERE the.id_stage=:id_stage ORDER BY the.rank ASC ";
$REQUEST_106 = " SELECT dat.label, lis.label, dat.comment, dat.grade, lis.id_list
					FROM eai_appraisal_data as dat, eai_criterion as cri, eai_grade as gra, eai_list as lis
					WHERE dat.id_eai=:id_eai 
					AND cri.id_theme=:id_theme 
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					ORDER BY cri.rank ASC";
$REQUEST_107 = " SELECT ead.label, ead.comment, ead.grade 
					FROM eai_appraisal_data as ead, eai_theme as the, eai_criterion as cri 
					WHERE ead.id_eai=:id_eai 
					AND the.id_theme=:id_theme 
					AND the.id_theme=cri.id_theme 
					AND cri.id_criterion=ead.id_criterion 
					ORDER BY the.rank ASC"; //AND ead.grade is NULL 
$REQUEST_108 = " SELECT 	DATE_FORMAT(app.date, '%d/%m/%Y'), 
							DATE_FORMAT(app.date_valid_manager, '%d/%m/%Y'), 
							DATE_FORMAT(app.date_valid_user, '%d/%m/%Y') , 
							app.id_status,
							uti.nom,  
							uti.prenom,
							uti2.nom,  
							uti2.prenom
					FROM 	eai_appraisal as app, adm_utilisateur as uti , adm_utilisateur as uti2 
					WHERE 	app.id_eai=:id_eai 
					AND 	app.id_user=uti.id_user
					AND 	app.id_manager=uti2.id_user";
$REQUEST_109 = " SELECT id_template, designation ,name  FROM  eai_template  ";
$REQUEST_110 = " SELECT tmp.name FROM eai_template as tmp WHERE tmp.id_template=:id_template ";
$REQUEST_111 = " SELECT cri.id_criterion, cri.label, cri.comment, cri.default_value FROM eai_criterion as cri WHERE cri.id_theme=:id_theme ORDER BY cri.rank ASC"; // image de $REQUEST_127
$REQUEST_112 = " SELECT cri.id_criterion, cri.label, lis.label, cri.comment, cri.id_grade, cri.default_value, gra.id_list
					FROM eai_criterion as cri, eai_grade as gra, eai_list as lis
					WHERE cri.id_theme=:id_theme
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND cri.default_value=lis.value
					ORDER BY cri.rank ASC"; // requête image à la requête 114 & 163 & 164
$REQUEST_113 = " SELECT  lis.value , lis.label  FROM eai_list as lis WHERE lis.id_list=:id_list ORDER BY lis.rank ASC";
$REQUEST_114 = " SELECT cri.id_criterion, cri.label, NULL , cri.comment, cri.id_grade, cri.default_value, NULL
					FROM eai_criterion as cri
					WHERE cri.id_theme=:id_theme
					AND cri.id_grade IS NULL
					ORDER BY cri.rank ASC"; // requête image à la requête 112 & 163 & 164
$REQUEST_115 = " DELETE FROM eai_appraisal WHERE id_user=:id_user ";
$REQUEST_116 = " DELETE FROM eai_appraisal_data WHERE id_eai = :id_eai ";
$REQUEST_117 = " SELECT id_eai FROM eai_appraisal WHERE id_user=:id_user ";
$REQUEST_118 = " DELETE FROM eai_template ";
$REQUEST_119 = " INSERT INTO eai_template (id_template, name, designation) VALUES (:id_eai_template, :name, :designation) ";
$REQUEST_120 = " SELECT id_criterion 
					FROM eai_stage as sta, eai_theme as the , eai_criterion as cri
					WHERE sta.id_template=:id_template 
					AND sta.id_stage = the.id_stage
					AND the.id_theme=cri.id_theme
					ORDER BY sta.rank, the.rank, cri.rank ASC ";
$REQUEST_121 = " UPDATE eai_criterion SET label=:label, default_value=:list_grade, comment=:comment WHERE id_criterion=:id_criterion ";
$REQUEST_122 = " SELECT 	uti.nom,  
							uti.prenom,
							DATE_FORMAT(CURDATE(), '%Y'),
							DATE_FORMAT(CURDATE(), '%d/%m/%Y'),
							uti.matricule,
							cab.designation,
							uti.fonction,
							prf.designation,
							uti2.nom,  
							uti2.prenom,
							uti2.nom,  
							uti2.prenom,
							uti.id_user,  
							uti.id_manager,
							NULL
					FROM 	adm_utilisateur as uti , adm_utilisateur as uti2  ,adm_client_arborescence as cab, adm_profil as prf  
					WHERE 	uti.id_user=:id_user
					AND 	uti.id_manager=uti2.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil ";
$REQUEST_123 = " INSERT INTO eai_appraisal ( id_user, id_manager, id_manager_interview, date,  id_template, id_status) VALUES (:id_user, :id_manager, :id_manager_interview, :date,  :id_template, :id_status)";
$REQUEST_124 = " SELECT max(id_eai) FROM eai_appraisal WHERE id_user=:id_user AND id_manager=:id_manager AND id_manager_interview=:id_manager_interview AND date=:date AND  id_template=:id_template AND id_status=:id_status "; //a coder car max est une astuce si la personne fait plusieurs EAI sur un jour l'algo plante
$REQUEST_125 = " INSERT INTO eai_appraisal_data ( id_eai, id_criterion, label, grade, comment) VALUES ( :id_eai, :id_criterion, :label, :grade, :comment) ";
$REQUEST_126 = " UPDATE eai_appraisal_data SET label=:label, grade=:grade, comment=:comment WHERE id_eai=:id_eai AND id_criterion=:id_criterion ";
$REQUEST_127 = " SELECT dat.id_criterion, dat.label, dat.comment, dat.grade
					FROM eai_appraisal_data as dat, eai_criterion as cri 
					WHERE dat.id_eai=:id_eai 
					AND cri.id_theme=:id_theme 
					AND dat.id_criterion = cri.id_criterion
					ORDER BY cri.rank ASC"; // image de $REQUEST_111
$REQUEST_128 = " SELECT cri.id_criterion, app.label, lis.label, app.comment, cri.id_grade, app.grade, gra.id_list
					FROM eai_appraisal_data as app, eai_criterion as cri, eai_grade as gra, eai_list as lis
					WHERE app.id_eai=:id_eai 
					AND cri.id_theme=:id_theme
					AND app.id_criterion=cri.id_criterion
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND cri.default_value=lis.value
					ORDER BY cri.rank ASC"; // requête image à la requête 112"; 
$REQUEST_129 = " UPDATE eai_appraisal SET  date_valid_manager=CURRENT_DATE, id_status=:id_status WHERE id_eai=:id_eai";
$REQUEST_130 = " UPDATE eai_appraisal SET  date_valid_user=CURRENT_DATE, id_status=:id_status WHERE id_eai=:id_eai";
$REQUEST_131 = " SELECT 	uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%Y'),
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							uti.matricule,
							cab.designation,
							uti.fonction,
							prf.designation,
							uti2.nom,  
							uti2.prenom,
							uti2.nom,  
							uti2.prenom,
							uti.id_user,  
							uti.id_manager,
							app.id_status
					FROM 	eai_appraisal as app, adm_utilisateur as uti , adm_utilisateur as uti2  ,adm_client_arborescence as cab, adm_profil as prf  
					WHERE   app.id_eai = :id_eai 	
					AND     app.id_user=uti.id_user
					AND 	uti.id_manager=uti2.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil ";
$REQUEST_132 = " UPDATE eai_appraisal SET  date=:date WHERE id_eai=:id_eai ";
$REQUEST_133 = " SELECT id_status FROM eai_appraisal WHERE id_eai=:id_eai ";
$REQUEST_134 = " SELECT 	app.id_eai,
							uti.id_user,
							uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%Y'),
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							uti.matricule,
							cab.designation,
							uti.fonction,
							prf.designation,  
							uti.id_manager
					FROM 	eai_appraisal as app, adm_utilisateur as uti, adm_client_arborescence as cab, adm_profil as prf  
					WHERE   app.id_manager=:id_user
					AND     app.id_status=:id_status
					AND     app.id_user=uti.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil 
					ORDER BY app.date DESC";
$REQUEST_135 = " UPDATE eai_appraisal_data SET comment=:comment WHERE id_eai=:id_eai AND id_criterion=:id_criterion ";
$REQUEST_136 = " DELETE
					FROM eai_appraisal_data 
					WHERE id_eai=:id_eai
					AND label = ''
					AND comment = '' OR comment IS NULL
					AND id_criterion IN (   SELECT cri.id_criterion
					                        FROM  eai_appraisal as app, eai_criterion as cri, eai_theme as the, eai_stage as sta
					                        WHERE app.id_eai=id_eai
					                        AND cri.id_theme <> :id_theme
					                        AND app.id_template = sta.id_template
					                        AND cri.id_theme=the.id_theme
					                        AND the.id_stage=sta.id_stage)";
$REQUEST_137 = " DELETE FROM eai_appraisal WHERE id_eai = :id_eai ";
$REQUEST_138 = " SELECT 	app.id_eai,
							uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							case 
                                when app.id_status=1 then 'En cours'
                                when app.id_status=2 then 'En attente de validation par le collaborateur'
                                when app.id_status=3 then 'Clôturé'
                                when app.id_status=4 then 'Clôturé non signé par le manageur'
                                when app.id_status=5 then 'Clôturé non signé par le collaborateur'
                            END AS status,
							uti.id_user
					FROM 	eai_appraisal as app, adm_utilisateur as uti
					WHERE   app.id_status=:id_status
					AND     app.id_user=uti.id_user
					ORDER BY app.date DESC ";

//WHERE   ( app.id_status=2 OR app.id_status=3 OR app.id_status=4)
$REQUEST_139 = " SELECT 	app.id_eai,
							uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							uti.id_user
					FROM 	eai_appraisal as app, adm_utilisateur as uti
					WHERE   ( app.id_status>=2)
					AND     app.id_user=uti.id_user
					ORDER BY app.date DESC";
$REQUEST_140 = " UPDATE eai_appraisal SET  date_valid_manager=NULL, date_valid_user=NULL, id_status=1 WHERE id_eai=:id_eai";
/*
  $REQUEST_141 	= " SELECT  cri2.id_criterion ,dat.label,  lis.label, cri2.comment, cri2.id_grade, cri2.default_value, gra.id_list
  FROM    eai_appraisal_data as dat, eai_criterion as cri,eai_criterion as cri2, eai_grade as gra, eai_list as lis
  WHERE   dat.id_eai = (  SELECT   max(app.id_eai)
  FROM eai_appraisal as app
  WHERE   app.id_user = (   SELECT  id_user
  FROM    eai_appraisal as app3
  WHERE   app3.id_eai=:id_eai   )
  AND     app.date = (    SELECT max(app2.date)
  FROM eai_appraisal as app2
  WHERE app2.id_user= (   SELECT  id_user
  FROM    eai_appraisal as app3
  WHERE   app3.id_eai=:id_eai   )
  AND app2.id_eai<>:id_eai
  )
  )
  AND     cri.id_theme=:id_theme
  AND     cri2.id_theme=:id_theme_2
  AND     cri.rank=cri2.rank
  AND     dat.id_criterion=cri.id_criterion
  AND     cri2.id_grade=gra.id_grade
  AND     gra.id_list=lis.id_list
  AND    cri2.default_value=lis.value
  ORDER BY cri.rank ASC";//requête spéciale qui récupère les objectifs de la précédente évaluation si elle existe
 */
$REQUEST_141 = " (
					SELECT  cri2.id_criterion as id_crit ,dat.label,  lis.label, cri2.comment, cri2.id_grade, cri2.default_value, gra.id_list
					FROM    eai_appraisal_data as dat, eai_criterion as cri,eai_criterion as cri2, eai_grade as gra, eai_list as lis
					WHERE   dat.id_eai = (  SELECT   max(app.id_eai)
					                        FROM eai_appraisal as app 
					                        WHERE   app.id_user = (   SELECT  id_user 
														                                FROM    eai_appraisal as app3 
														                                WHERE   app3.id_eai=:id_eai   )
					                        AND     app.date = (    SELECT max(app2.date) 
											                        FROM eai_appraisal as app2
												                    WHERE app2.id_user= (   SELECT  id_user 
														                                FROM    eai_appraisal as app3 
														                                WHERE   app3.id_eai=:id_eai   )
												                    AND app2.id_eai<>:id_eai
								AND app.id_eai<>:id_eai		                     )
					                     )
					AND     cri.id_theme=:id_theme
					AND     cri2.id_theme=:id_theme_2
					AND     cri.rank=cri2.rank
					AND     dat.id_criterion=cri.id_criterion
					AND     cri2.id_grade=gra.id_grade
					AND     gra.id_list=lis.id_list
					AND    cri2.default_value=lis.value
					)
					UNION
					(SELECT cri.id_criterion as id_crit, cri.label, lis.label, cri.comment, cri.id_grade, cri.default_value, gra.id_list
										FROM eai_criterion as cri, eai_theme as the, eai_grade as gra, eai_list as lis
										WHERE the.id_theme=:id_theme_2
										AND cri.id_criterion NOT IN (
											SELECT  dat.id_criterion
											FROM    eai_appraisal_data as dat
											WHERE   dat.id_eai = (  SELECT   max(app.id_eai)
											                        FROM eai_appraisal as app 
											                        WHERE   app.id_user = ( SELECT  id_user 
																				                FROM    eai_appraisal as app3 
																				                WHERE   app3.id_eai=:id_eai   )
											                        AND     app.date    = ( SELECT max(app2.date) 
																	                        FROM eai_appraisal as app2
																		                    WHERE app2.id_user= (   SELECT  id_user 
																				                                FROM    eai_appraisal as app3 
																				                                WHERE   app3.id_eai=:id_eai   )
																		                    AND app2.id_eai<>:id_eai
																                        )
											                     )
										)
										AND cri.id_theme=the.id_theme
										AND cri.id_grade=gra.id_grade
										AND gra.id_list=lis.id_list
										AND cri.default_value=lis.value
					ORDER BY id_crit  ASC
					LIMIT 5)
					";

$REQUEST_142 = "SELECT id_campaign, DATE_FORMAT(date_start,'%d/%m/%Y'), DATE_FORMAT(date_end,'%d/%m/%Y') FROM eai_campaign ORDER BY id_campaign DESC LIMIT 1";
$REQUEST_143 = "INSERT INTO eai_campaign (date_start, date_end) VALUES (:date_start, :date_end)";
$REQUEST_144 = "UPDATE eai_campaign SET date_start=:date_start,date_end=:date_end WHERE id_campaign=:id_campaign ";
$REQUEST_145 = " SELECT  id_user , prenom, nom , matricule
					FROM    adm_utilisateur 
					WHERE   id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					"; //image de REQUEST_152
$REQUEST_146 = " SELECT id_user, adm.prenom, adm.nom , adm.matricule
					FROM adm_utilisateur as adm
					WHERE id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND id_user NOT IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
										)
					ORDER BY nom ASC "; //image de REQUEST_153
$REQUEST_147 = " SELECT adm.id_user, adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status < 2)
                    AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status < 2)
					ORDER BY adm.nom ASC "; //image de REQUEST_154
$REQUEST_148 = " SELECT adm.id_user , adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 2
										)
					 AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 2)
					ORDER BY adm.nom ASC "; //image de REQUEST_155
$REQUEST_149 = " SELECT adm.id_user , adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 3)
					AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 3)
					ORDER BY adm.nom ASC "; //image de REQUEST_156
/* $REQUEST_150 	= " SELECT app1.id_eai, CONCAT(prenom,' ', nom), matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
  FROM adm_utilisateur as adm, eai_appraisal as app1
  WHERE adm.id_manager IS NOT NULL
  AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
  AND adm.id_user IN (
  SELECT id_user
  FROM  eai_appraisal as app, eai_campaign
  WHERE id_campaign = :id_campaign
  AND app.id_user = adm.id_user
  AND app.date BETWEEN date_start AND date_end
  AND app.id_status = 3)
  AND app1.id_eai IN (
  SELECT id_eai
  FROM  eai_appraisal as app, eai_campaign
  WHERE id_campaign = :id_campaign
  AND app.id_user = adm.id_user
  AND app.date BETWEEN date_start AND date_end
  AND app.id_status = 3)
  ORDER BY nom ASC , app1.date DESC"; */
$REQUEST_150 = " SELECT app1.id_eai, CONCAT(prenom,' ', nom), matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager IS NOT NULL 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE app.id_user = adm.id_user 
							AND app.id_status = 3)
					AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE app.id_user = adm.id_user 
							AND app.id_status = 3)
					ORDER BY nom ASC , app1.date DESC";
$REQUEST_151 = " SELECT  app1.id_eai, CONCAT(adm.prenom,' ', adm.nom), adm.matricule, CONCAT(prf.designation,' ',adm_1.prenom,' ',.adm_1.nom), DATE_FORMAT(app1.date, '%d/%m/%Y') ,
							case 
                                when app1.id_status=1 then 'En cours'
                                when app1.id_status=2 then 'En attente de validation par le collaborateur'
                                when app1.id_status=3 then 'Clôturé'
                                when app1.id_status=4 then 'Clôturé non signé par le manageur'
                                when app1.id_status=5 then 'Clôturé non signé par le collaborateur'
                            END AS status
					FROM adm_utilisateur as adm,adm_utilisateur as adm_1, eai_appraisal as app1,adm_profil as prf 
					WHERE adm.id_manager = adm_1.id_user
					AND adm_1.id_profil=prf.id_profil
					AND (adm.date_sortie_societe IS NULL OR adm.date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status IN ( 1,2,3 ))
					AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status IN ( 1,2,3 ))
					ORDER BY adm.nom ASC, app1.date DESC "; // A CODER : les statuts des EAI devraient faire l'object d'un table de données pour ne pas bloquer le multi langue (si un jour cela est demandé)
$REQUEST_152 = " SELECT  adm.id_user , adm.prenom, adm.nom , adm.matricule
					FROM    adm_utilisateur as adm
					WHERE   id_manager= :id_manager 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					ORDER BY nom ASC "; //image de REQUEST_145
$REQUEST_153 = " SELECT adm.id_user, adm.prenom, adm.nom , adm.matricule
					FROM adm_utilisateur as adm
					WHERE id_manager = :id_manager 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND id_user NOT IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
										)
					ORDER BY adm.nom ASC "; //image de REQUEST_146
$REQUEST_154 = " SELECT adm.id_user, adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager = :id_manager 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status < 2)
                    AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status < 2)
					ORDER BY adm.nom ASC "; //image de REQUEST_147
$REQUEST_155 = " SELECT adm.id_user , adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager = :id_manager 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 2
										)
					 AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 2)
					ORDER BY adm.nom ASC "; //image de REQUEST_148
$REQUEST_156 = " SELECT adm.id_user , adm.prenom, adm.nom, app1.id_eai, adm.matricule, DATE_FORMAT(app1.date, '%d/%m/%Y')
					FROM adm_utilisateur as adm, eai_appraisal as app1
					WHERE adm.id_manager = :id_manager 
					AND (date_sortie_societe IS NULL OR date_sortie_societe > NOW())
					AND adm.id_user IN (
							SELECT id_user 
							FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 3)
					AND app1.id_eai IN (
							SELECT id_eai							
                            FROM  eai_appraisal as app, eai_campaign  
							WHERE id_campaign = :id_campaign
							AND app.id_user = adm.id_user 
							AND app.date BETWEEN date_start AND date_end
							AND app.id_status = 3)
					ORDER BY adm.nom ASC "; //image de REQUEST_149
$REQUEST_157 = " SELECT uti.id_user FROM adm_utilisateur as uti, eai_appraisal as app WHERE uti.id_user=:id_user AND uti.id_user=app.id_user AND app.date BETWEEN :date_start AND :date_end ";
$REQUEST_158 = " UPDATE eai_appraisal SET id_status=4 WHERE id_status=0 OR id_status=1 ";
$REQUEST_159 = " UPDATE eai_appraisal SET id_status=5 WHERE id_status=2 ";
$REQUEST_160 = " SELECT 	count(uti.id_user) 
					FROM 	eai_appraisal as app, adm_utilisateur as uti, adm_client_arborescence as cab, adm_profil as prf  
					WHERE   app.id_manager=:id_user
					AND     app.id_status=:id_status
					AND     app.id_user=uti.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil 
					ORDER BY app.date DESC";
$REQUEST_161 = " SELECT id_theme, rank, name, id_stage, activate FROM eai_theme WHERE id_theme=:id_theme";
$REQUEST_162 = " SELECT cri.id_criterion, cri.label, cri.comment, cri.default_value FROM eai_criterion as cri, eai_theme as the WHERE the.id_theme=:id_theme AND the.activate = 1 AND the.id_theme=cri.id_theme ORDER BY cri.rank ASC "; //image de la requeet 111
$REQUEST_163 = " SELECT cri.id_criterion, cri.label, lis.label, cri.comment, cri.id_grade, cri.default_value, gra.id_list
					FROM eai_criterion as cri, eai_theme as the, eai_grade as gra, eai_list as lis
					WHERE the.id_theme=:id_theme
					AND the.activate=1
					AND cri.id_theme=the.id_theme
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND cri.default_value=lis.value
					ORDER BY cri.rank ASC"; // requête image à la requête 112 & 114
$REQUEST_164 = " SELECT cri.id_criterion, cri.label, NULL , cri.comment, cri.id_grade, cri.default_value, NULL
					FROM eai_criterion as cri, eai_theme as the
					WHERE the.id_theme=:id_theme
					AND the.activate=1
					AND cri.id_theme=the.id_theme
					AND cri.id_grade IS NULL
					ORDER BY cri.rank ASC"; // requête image à la requête 112 & 114 & 163 & 164
$REQUEST_165 = " UPDATE eai_stage SET activate=:activate WHERE id_stage=:id_stage ";
$REQUEST_166 = " UPDATE eai_theme SET activate=:activate WHERE id_theme=:id_theme ";
$REQUEST_167 = " SELECT DATE_FORMAT(app.date, '%Y') as annee
					FROM eai_appraisal as app
					GROUP BY annee
					ORDER BY annee  DESC ";
$REQUEST_168 = " UPDATE eai_appraisal_data SET grade=:grade, comment=:comment WHERE id_eai=:id_eai AND id_criterion=:id_criterion ";
$REQUEST_169 = " SELECT id_eai FROM  eai_appraisal_data WHERE id_eai=:id_eai AND id_criterion=:id_criterion";
$REQUEST_170 = " SELECT name FROM eai_template WHERE id_template=:id_template";
$REQUEST_171 = " SELECT id_stage FROM eai_stage WHERE id_template=:id_template and rank=:rank_stage";
$REQUEST_172 = " SELECT id_theme FROM eai_theme WHERE rank=:rank_theme AND id_stage=:id_stage";
$REQUEST_173 = " SELECT id_template FROM eai_appraisal WHERE id_eai=:id_eai";
$REQUEST_174 = "select th.id_theme from eai_theme as th 
                        WHERE th.id_stage =				
                        (	select st.id_stage from eai_stage as st 
                                WHERE id_template=				
                                (	select ap.id_template FROM eai_appraisal as ap 
                                        WHERE ap.id_eai = (  SELECT   max(app.id_eai)
                                        FROM eai_appraisal as app 
                                        WHERE   app.id_user = (   SELECT  id_user 
                                                                                        FROM    eai_appraisal as app3 
                                                                                        WHERE   app3.id_eai=:id_eai   )
                                        AND     app.date = (    SELECT max(app2.date) 
                                                        FROM eai_appraisal as app2
                                                            WHERE app2.id_user= (   SELECT  id_user 
                                                                                        FROM    eai_appraisal as app3 
                                                                                        WHERE   app3.id_eai=:id_eai   )
                                                            AND app2.id_eai<>:id_eai
                                        AND app.id_eai<>:id_eai		                     )
                                )
                        ) 
                        and st.rank=:rank
                        ) ";
$REQUEST_175 = "SELECT   max(app.id_eai)
                    FROM eai_appraisal as app 
                    WHERE   app.id_user = ( SELECT  id_user 
                                            FROM    eai_appraisal as app3 
                                            WHERE   app3.id_eai=:id_eai   )
                    AND     app.date = (    SELECT max(app2.date) 
                                            FROM eai_appraisal as app2
                                            WHERE app2.id_user= (   SELECT  id_user 
                                                                    FROM    eai_appraisal as app3 
                                                                    WHERE   app3.id_eai=:id_eai   )
                    AND app2.id_eai<>:id_eai
                    AND app.id_eai<>:id_eai		                     )";
$REQUEST_176 = " UPDATE eai_stage SET name=:title WHERE id_stage=:id_stage AND id_template=:id_template";
$REQUEST_177 = " SELECT 	app.id_eai,
							uti.id_user,
							uti.nom,  
							uti.prenom,
							DATE_FORMAT(app.date, '%Y'),
							DATE_FORMAT(app.date, '%d/%m/%Y'),
							uti.matricule,
							cab.designation,
							uti.fonction,
							prf.designation,  
							uti.id_manager
					FROM 	eai_appraisal as app, adm_utilisateur as uti, adm_client_arborescence as cab, adm_profil as prf  
					WHERE   app.id_manager=:id_manager
                                        AND     app.id_user=:id_user
					AND     app.id_status=:id_status
					AND     app.id_user=uti.id_user
					AND 	uti.id_noeud=cab.id_noeud 
					AND 	uti.id_profil=prf.id_profil 
					ORDER BY app.date DESC";
$REQUEST_178 = " SELECT count(*) FROM adm_eai_client WHERE id_compte_client=:id_client AND autoeval=1";
$REQUEST_179 = " SELECT dat.id_criterion, dat.label, dat.comment, dat.grade
					FROM eai_appraisal_data as dat, eai_criterion as cri 
					WHERE dat.id_eai=(SELECT id_eai FROM eai_appraisal as app WHERE app.id_user = (SELECT id_user FROM eai_appraisal WHERE id_eai=:id_eai)  AND app.id_eai!=:id_eai ORDER BY date DESC LIMIT 1)
					AND cri.id_theme=:id_theme 
					AND dat.id_criterion = cri.id_criterion
					ORDER BY cri.rank ASC";
$REQUEST_180 = " SELECT cri.id_criterion, app.label, lis.label, '' as comment, cri.id_grade, 3 as grade, gra.id_list
					FROM eai_appraisal_data as app, eai_criterion as cri, eai_grade as gra, eai_list as lis
					WHERE app.id_eai=(SELECT id_eai FROM eai_appraisal as app WHERE app.id_user = (SELECT id_user FROM eai_appraisal WHERE id_eai=:id_eai)  AND app.id_eai!=:id_eai ORDER BY date DESC LIMIT 1)
					AND cri.id_theme=:id_theme 
					AND app.id_criterion=cri.id_criterion
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND cri.default_value=lis.value
					ORDER BY cri.rank ASC;";
//============================================================================
// REQUESTS EAI - TABLEAUX DE BORD - a voir si on éclate plus tard pour mettre cette partie dans le module EAI...plus logique
//============================================================================
$REQUEST_200_1 = " SELECT the.name, AVG(dat.grade), DATE_FORMAT(app.date, '%Y')
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (   3,31,59,87,115,143,171,199,227,255,
											4,32,60,88,116,144,172,200,228,256,
											5,33,61,89,117,145,173,201,229,257,
											6,34,62,90,118,146,174,202,230,258,
											7,35,63,91,119,147,175,203,231,259,
											8,36,64,92,120,148,176,204,232,260,
                                                                                        27,40,68,96,124,152,180,208,236,264) 
					AND DATE_FORMAT(app.date, '%Y') = :annee ";
$REQUEST_200_2 = " AND app.id_eai = dat.id_eai
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND app.id_user=uti.id_user
                                        AND dat.grade!=0
					GROUP BY the.name
					ORDER BY the.rank  ASC ";
$REQUEST_201_1 = " SELECT the.name, CONCAT(uti.nom, ' ',uti.prenom) as cname, dat.label, DATE_FORMAT(app.date, '%Y'), dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis 
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (   3,31,59,87,115,143,171,199,227,255,
											4,32,60,88,116,144,172,200,228,256,
											5,33,61,89,117,145,173,201,229,257,
											6,34,62,90,118,146,174,202,230,258,
											7,35,63,91,119,147,175,203,231,259,
											8,36,64,92,120,148,176,204,232,260,
                                                                                        27,40,68,96,124,152,180,208,236,264) ";
$REQUEST_201_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value                                        
                                        AND dat.grade!=0
					ORDER BY the.name ASC , cname ASC , dat.label ASC ,app.date  DESC ";

$REQUEST_202_1 = " SELECT   lis.label, 
							(   SELECT count(dat.grade)
								FROM eai_appraisal as app, eai_appraisal_data as dat , adm_utilisateur as uti
								WHERE   app.id_status IN (3,4,5)
								AND     DATE_FORMAT(app.date, '%Y') = :annee
								AND     dat.id_criterion IN (79,241,393,545,697,849,1001,1153,1305,1457) ";
$REQUEST_202_2 = " 			AND     dat.id_eai = app.id_eai
								AND     app.id_user=uti.id_user 
								AND     dat.grade=lis.value),
							DATE_FORMAT(app.date, '%Y')
					FROM eai_appraisal as app, eai_theme as the,  eai_criterion as cri, eai_grade as gra, eai_list as lis, adm_utilisateur as uti
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (13,42,70,98,126,154,182,210,238,266) 
					AND DATE_FORMAT(app.date, '%Y') = :annee 
					AND cri.id_criterion IN (79,241,393,545,697,849,1001,1153,1305,1457) ";
$REQUEST_202_3 = " AND cri.id_theme=the.id_theme
					AND app.id_user=uti.id_user 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					GROUP BY lis.label
					ORDER BY lis.rank ASC, app.date DESC";

$REQUEST_203_1 = " SELECT cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname, DATE_FORMAT(app.date, '%Y'), lis2.label, lis.label, dat.comment
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab , eai_appraisal_data as dat2, eai_criterion as cri2, eai_grade as gra2, eai_list as lis2
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (13,42,70,98,126,154,182,210,238,266) 
					AND cri.id_criterion IN (79,241,393,545,697,849,1001,1153,1305,1457) ";
$REQUEST_203_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
                    AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion       
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
                    AND app.id_eai = dat2.id_eai
                    AND cri2.id_theme=the.id_theme
					AND cri2.id_criterion=dat2.id_criterion 
                    AND cri2.id_criterion IN (78,240,392,544,696,848,1000,1152,1304,1456)
					AND cri2.id_grade=gra2.id_grade
					AND gra2.id_list=lis2.id_list
					AND dat2.grade=lis2.value
					ORDER BY cab.designation ASC, cname ASC, app.date  DESC ";
$REQUEST_204_1 = " SELECT  lis.label, (SELECT count(dat.grade)
										FROM  eai_appraisal as app, eai_appraisal_data as dat, adm_utilisateur as uti 
										WHERE   app.id_status IN (3,4,5)
										AND     DATE_FORMAT(app.date, '%Y') = :annee ";
$REQUEST_204_2 = " 				    AND     dat.id_eai = app.id_eai
										AND     dat.id_criterion IN (   73,235,387,539,691,843,995,1147,1299,1451,
																		74,236,388,540,692,844,996,1148,1300,1452,
																		75,237,389,541,693,845,997,1149,1301,1453,
																		76,238,390,542,694,846,998,1150,1302,1454,
																		77,239,391,543,695,847,999,1151,1303,1455)
										AND     dat.grade=lis.value
										AND     app.id_user=uti.id_user ),
							DATE_FORMAT(app.date, '%Y')					
					FROM eai_appraisal as app, eai_theme as the,  eai_criterion as cri, eai_grade as gra, eai_list as lis, adm_utilisateur as uti
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (12,41,69,97,125,153,181,209,237,265) 
					AND     DATE_FORMAT(app.date, '%Y') = :annee 
					AND     cri.id_criterion IN (   73,235,387,539,691,843,995,1147,1299,1451,
													74,236,388,540,692,844,996,1148,1300,1452,
													75,237,389,541,693,845,997,1149,1301,1453,
													76,238,390,542,694,846,998,1150,1302,1454,
													77,239,391,543,695,847,999,1151,1303,1455) ";
$REQUEST_204_3 = " AND     cri.id_theme=the.id_theme
					AND     app.id_user=uti.id_user 
					AND     cri.id_grade=gra.id_grade
					AND     gra.id_list=lis.id_list
					GROUP BY lis.label
					ORDER BY lis.rank ASC";
$REQUEST_205_1 = " SELECT cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname, DATE_FORMAT(app.date, '%Y'), dat.label, lis.label, dat.comment
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab 
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (12,41,69,97,125,153,181,209,237,265) ";
$REQUEST_205_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
                    AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					ORDER BY cab.designation ASC , cname ASC ,app.date  DESC  , dat.label ASC ";
$REQUEST_206_1 = " SELECT  lis.label, (SELECT count(dat.grade)
										FROM eai_appraisal as app, eai_appraisal_data as dat , adm_utilisateur as uti
										WHERE   app.id_status IN (3,4,5)
										AND     DATE_FORMAT(app.date, '%Y') = :annee
										AND     dat.id_criterion IN (78,240,392,544,696,848,1000,1152,1304,1456) ";
$REQUEST_206_2 = "                     AND     dat.id_eai = app.id_eai
										AND     app.id_user=uti.id_user 
										AND     dat.grade=lis.value),
					        DATE_FORMAT(app.date, '%Y') 
					FROM eai_appraisal as app, eai_theme as the,  eai_criterion as cri, eai_grade as gra, eai_list as lis, adm_utilisateur as uti
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (13,42,70,98,126,154,182,210,238,266) 
					AND     DATE_FORMAT(app.date, '%Y') = :annee
					AND     cri.id_criterion IN (78,240,392,544,696,848,1000,1152,1304,1456) ";
$REQUEST_206_3 = " AND     cri.id_grade=gra.id_grade
					AND     app.id_user=uti.id_user 
					AND     gra.id_list=lis.id_list
					AND     cri.id_theme=the.id_theme
					GROUP BY lis.label
					ORDER BY lis.rank ASC, app.date DESC";
$REQUEST_207_1 = "SELECT cab2.designation, 
							(   SELECT count(dat.label)
								FROM eai_appraisal as app, eai_appraisal_data as dat , adm_utilisateur as uti, adm_client_arborescence as cab 
								WHERE   app.id_status IN (3,4,5)
								AND     DATE_FORMAT(app.date, '%Y') = :annee
								AND     dat.id_criterion IN (   127,294,446,598,750,902,1054,1206,1358,1510,
																128,295,447,599,751,903,1055,1207,1359,1511,
																129,296,448,600,752,904,1056,1208,1360,1512,
																130,297,449,601,753,905,1057,1209,1361,1513,
																131,298,450,602,754,906,1058,1210,1362,1514) ";
$REQUEST_207_2 = "				AND     dat.id_eai = app.id_eai
								AND     app.id_user=uti.id_user 
								AND     cab.id_noeud = uti.id_noeud
								AND     cab2.id_noeud = uti.id_noeud) as nb,
							DATE_FORMAT(app.date, '%Y')
					FROM    eai_appraisal as app, adm_client_arborescence as cab2 , adm_utilisateur as uti
					WHERE   app.id_status IN (3,4,5)
					AND     DATE_FORMAT(app.date, '%Y') = :annee ";
$REQUEST_207_3 = " AND     uti.id_user = app.id_user
					GROUP BY cab2.designation
					ORDER BY cab2.id_noeud ASC,  nb DESC , app.date DESC";
$REQUEST_208_1 = " SELECT cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname, DATE_FORMAT(app.date, '%Y'), dat.label, DATE_FORMAT(dat.grade, '%d/%m/%Y') 
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, adm_client_arborescence as cab 
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (22,53,81,109,137,165,193,221,249,277) ";
$REQUEST_208_2 = " AND     app.id_eai = dat.id_eai
					AND     app.id_user = uti.id_user
					AND     uti.id_noeud=cab.id_noeud
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
					ORDER BY cab.designation ASC , cname ASC , app.date  DESC  ";
$REQUEST_209_1 = " SELECT lis.label, count(lis.label),dat.label,dat.grade
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, eai_grade as gra, eai_list as lis
					WHERE   DATE_FORMAT(app.date, '%Y') = :annee
					AND     app.id_status IN (3,4,5)
					AND     the.id_theme IN (15,45,73,101,129,157,185,213,241,269) ";
$REQUEST_209_2 = " AND     app.id_eai = dat.id_eai
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
                    AND     cri.id_grade=gra.id_grade
					AND     gra.id_list=lis.id_list
					AND     dat.grade=lis.value
					GROUP BY lis.label
					ORDER BY cri.rank ASC , lis.rank ASC "; //ATTENTION les critères doivent avoir le même libellé d'un modèle à l'autre
$REQUEST_210 = " SELECT cri.id_criterion, dat.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri
					WHERE app.id_manager=:id_manager
                    AND DATE_FORMAT(app.date, '%Y') = :annee
					AND app.id_status IN (3,4,5)
					AND the.id_theme IN (15,45,73,101,129,157,185,213,241,269) 
					AND app.id_eai = dat.id_eai
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
                    GROUP BY dat.label
                    ORDER BY cri.rank , app.date ";
$REQUEST_211_1 = " SELECT cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname, DATE_FORMAT(app.date, '%Y'), dat.label, lis.label, dat.comment
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab 
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (15,45,73,101,129,157,185,213,241,269) ";
$REQUEST_211_2 = " AND     app.id_eai = dat.id_eai
					AND     app.id_user = uti.id_user
                    AND     uti.id_noeud=cab.id_noeud
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
					AND     cri.id_grade=gra.id_grade
					AND     gra.id_list=lis.id_list
					AND     dat.grade=lis.value
					ORDER BY cab.designation ASC , cname ASC ,app.date  DESC, cri.rank ASC  ";
$REQUEST_212 = " SELECT  cri.id_criterion, dat.label
					FROM    eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (15,45,73,101,129,157,185,213,241,269) 
					AND     app.id_eai = dat.id_eai
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
                    GROUP BY dat.label
                    ORDER BY cri.rank ASC, app.date";
$REQUEST_213_1 = " SELECT  concat(dat.label,'  '), count(dat.label) as nb, DATE_FORMAT(app.date, '%Y')
					FROM eai_appraisal as app, eai_appraisal_data as dat ,eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti
					WHERE app.id_status IN (3,4,5)
					AND DATE_FORMAT(app.date, '%Y') = :annee 
					AND the.id_theme IN (19,49,77,105,133,161,189,217,245,273,29,50,78,106,134,162,190,218,246,274) 
					AND cri.id_criterion IN (   112,274,426,578,730,882,1034,1186,1338,1490,
									            113,275,427,579,731,883,1035,1187,1339,1491,
									            114,276,428,580,732,884,1036,1188,1340,1492,
									            115,277,429,581,733,885,1037,1189,1341,1493,
								                116,278,430,582,734,886,1038,1190,1342,1494,
								                150,279,431,583,735,887,1039,1191,1343,1495,
									            151,280,432,584,736,888,1040,1192,1344,1496,
									            152,281,433,585,737,889,1041,1193,1345,1497,
									            153,282,434,586,738,890,1042,1194,1346,1498,
								                154,283,435,587,739,891,1043,1195,1347,1499) ";
$REQUEST_213_2 = " AND dat.id_eai = app.id_eai
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion
					AND app.id_user=uti.id_user
					GROUP BY dat.label
					ORDER BY nb DESC, app.date DESC, cri.id_criterion ASC ";

$REQUEST_214_1 = " SELECT  concat(dat.label,'  '), count(dat.label) as nb, DATE_FORMAT(app.date, '%Y')
					FROM eai_appraisal as app, eai_appraisal_data as dat ,eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti
					WHERE app.id_status IN (3,4,5)
					AND DATE_FORMAT(app.date, '%Y') = :annee 
					AND the.id_theme IN ( 20,51,79,107,135,163,191,219,247,275,21,52,80,108,136,164,192,220,248,276) 
					AND cri.id_criterion IN (   117,284,436,588,740,892,1044,1196,1348,1500,
												118,285,437,589,741,893,1045,1197,1349,1501,
												119,286,438,590,742,894,1046,1198,1350,1502,
												120,287,439,591,743,895,1047,1199,1351,1503,
												121,288,440,592,744,896,1048,1200,1352,1504,
												122,289,441,593,745,897,1049,1201,1353,1505,
												123,290,442,594,746,898,1050,1202,1354,1506,
												124,291,443,595,747,899,1051,1203,1355,1507,
												125,292,444,596,748,900,1052,1204,1356,1508,
												126,293,445,597,749,901,1053,1205,1357,1509) ";
$REQUEST_214_2 = " AND dat.id_eai = app.id_eai
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion
					AND app.id_user=uti.id_user
					GROUP BY dat.label
					ORDER BY nb DESC, app.date DESC, cri.id_criterion ASC ";

$REQUEST_215_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname,  dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab 
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (19,49,77,105,133,161,189,217,245,273,29,50,78,106,134,162,190,218,246,274)
					AND cri.id_criterion IN (   112,274,426,578,730,882,1034,1186,1338,1490,
									            113,275,427,579,731,883,1035,1187,1339,1491,
									            114,276,428,580,732,884,1036,1188,1340,1492,
									            115,277,429,581,733,885,1037,1189,1341,1493,
								                116,278,430,582,734,886,1038,1190,1342,1494,
								                150,279,431,583,735,887,1039,1191,1343,1495,
									            151,280,432,584,736,888,1040,1192,1344,1496,
									            152,281,433,585,737,889,1041,1193,1345,1497,
									            153,282,434,586,738,890,1042,1194,1346,1498,
								                154,283,435,587,739,891,1043,1195,1347,1499) ";
$REQUEST_215_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					ORDER BY app.date  DESC, cab.designation ASC , cname ASC , dat.label ASC ";

$REQUEST_216_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti.nom, ' ',uti.prenom) as cname,  dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab 
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (20,51,79,107,135,163,191,219,247,275,21,52,80,108,136,164,192,220,248,276)
					AND cri.id_criterion IN (   117,284,436,588,740,892,1044,1196,1348,1500,
												118,285,437,589,741,893,1045,1197,1349,1501,
												119,286,438,590,742,894,1046,1198,1350,1502,
												120,287,439,591,743,895,1047,1199,1351,1503,
												121,288,440,592,744,896,1048,1200,1352,1504,
												122,289,441,593,745,897,1049,1201,1353,1505,
												123,290,442,594,746,898,1050,1202,1354,1506,
												124,291,443,595,747,899,1051,1203,1355,1507,
												125,292,444,596,748,900,1052,1204,1356,1508,
												126,293,445,597,749,901,1053,1205,1357,1509) ";
$REQUEST_216_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					ORDER BY app.date  DESC, cab.designation ASC , cname ASC , dat.label ASC ";
$REQUEST_217_1 = " SELECT  concat(dat.label,'  '), 0 as nb, ' ' as date, count(dat.label) as tri
					FROM eai_appraisal as app, eai_appraisal_data as dat ,eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (19,49,77,105,133,161,189,217,245,273,29,50,78,106,134,162,190,218,246,274) 
					AND cri.id_criterion IN (   112,274,426,578,730,882,1034,1186,1338,1490,
									            113,275,427,579,731,883,1035,1187,1339,1491,
									            114,276,428,580,732,884,1036,1188,1340,1492,
									            115,277,429,581,733,885,1037,1189,1341,1493,
								                116,278,430,582,734,886,1038,1190,1342,1494,
								                150,279,431,583,735,887,1039,1191,1343,1495,
									            151,280,432,584,736,888,1040,1192,1344,1496,
									            152,281,433,585,737,889,1041,1193,1345,1497,
									            153,282,434,586,738,890,1042,1194,1346,1498,
								                154,283,435,587,739,891,1043,1195,1347,1499) ";
$REQUEST_217_2 = " AND dat.id_eai = app.id_eai
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion
					AND app.id_user=uti.id_user
					GROUP BY dat.label
					ORDER BY tri DESC, cri.id_criterion ASC
					LIMIT 10 ";
$REQUEST_218_1 = " SELECT DATE_FORMAT(app.date, '%Y'), the.name, arb.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname, dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis , adm_client_arborescence as arb, adm_utilisateur as uti_man
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (   3,31,59,87,115,143,171,199,227,255,
											4,32,60,88,116,144,172,200,228,256,
											5,33,61,89,117,145,173,201,229,257,
											6,34,62,90,118,146,174,202,230,258,
											7,35,63,91,119,147,175,203,231,259,
											8,36,64,92,120,148,176,204,232,260,
                                                                                        27,40,68,96,124,152,180,208,236,264) ";
$REQUEST_218_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND arb.id_noeud=uti.id_noeud
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					AND uti_man.id_user=uti.id_manager
                                        AND dat.grade!=0
                                        ORDER BY the.name ASC , cname ASC , dat.label ASC ,app.date  DESC ";

$REQUEST_219_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom) ,CONCAT(uti.nom, ' ',uti.prenom) as cname,  lis2.label, lis.label, dat.comment
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab , eai_appraisal_data as dat2, eai_criterion as cri2, eai_grade as gra2, eai_list as lis2, adm_utilisateur as uti_man 
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (13,42,70,98,126,154,182,210,238,266) 
					AND cri.id_criterion IN (79,241,393,545,697,849,1001,1153,1305,1457) ";
$REQUEST_219_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
                    AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion       
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
                    AND app.id_eai = dat2.id_eai
                    AND cri2.id_theme=the.id_theme
					AND cri2.id_criterion=dat2.id_criterion 
                    AND cri2.id_criterion IN (78,240,392,544,696,848,1000,1152,1304,1456)
					AND cri2.id_grade=gra2.id_grade
					AND gra2.id_list=lis2.id_list
					AND dat2.grade=lis2.value
					AND uti_man.id_user=uti.id_manager
					ORDER BY cab.designation ASC, cname ASC, app.date  DESC ";

$REQUEST_220_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname,  dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab, adm_utilisateur as uti_man  
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (19,49,77,105,133,161,189,217,245,273,29,50,78,106,134,162,190,218,246,274)
					AND cri.id_criterion IN (   112,274,426,578,730,882,1034,1186,1338,1490,
									            113,275,427,579,731,883,1035,1187,1339,1491,
									            114,276,428,580,732,884,1036,1188,1340,1492,
									            115,277,429,581,733,885,1037,1189,1341,1493,
								                116,278,430,582,734,886,1038,1190,1342,1494,
								                150,279,431,583,735,887,1039,1191,1343,1495,
									            151,280,432,584,736,888,1040,1192,1344,1496,
									            152,281,433,585,737,889,1041,1193,1345,1497,
									            153,282,434,586,738,890,1042,1194,1346,1498,
								                154,283,435,587,739,891,1043,1195,1347,1499) ";
$REQUEST_220_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					AND uti_man.id_user=uti.id_manager
					ORDER BY app.date  DESC, cab.designation ASC , cname ASC , dat.label ASC ";
$REQUEST_221_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname,  dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab, adm_utilisateur as uti_man   
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (20,51,79,107,135,163,191,219,247,275,21,52,80,108,136,164,192,220,248,276)
					AND cri.id_criterion IN (   117,284,436,588,740,892,1044,1196,1348,1500,
												118,285,437,589,741,893,1045,1197,1349,1501,
												119,286,438,590,742,894,1046,1198,1350,1502,
												120,287,439,591,743,895,1047,1199,1351,1503,
												121,288,440,592,744,896,1048,1200,1352,1504,
												122,289,441,593,745,897,1049,1201,1353,1505,
												123,290,442,594,746,898,1050,1202,1354,1506,
												124,291,443,595,747,899,1051,1203,1355,1507,
												125,292,444,596,748,900,1052,1204,1356,1508,
												126,293,445,597,749,901,1053,1205,1357,1509) ";
$REQUEST_221_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
					AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					AND uti_man.id_user=uti.id_manager
					ORDER BY app.date  DESC, cab.designation ASC , cname ASC , dat.label ASC ";

$REQUEST_222_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname, dat.label, dat.comment, lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab, adm_utilisateur as uti_man    
					WHERE app.id_status IN (3,4,5)
					AND the.id_theme IN (12,41,69,97,125,153,181,209,237,265) ";
$REQUEST_222_2 = " AND app.id_eai = dat.id_eai
					AND app.id_user = uti.id_user
                    AND uti.id_noeud=cab.id_noeud
					AND cri.id_theme=the.id_theme
					AND cri.id_criterion=dat.id_criterion 
					AND cri.id_grade=gra.id_grade
					AND gra.id_list=lis.id_list
					AND dat.grade=lis.value
					AND uti_man.id_user=uti.id_manager
					ORDER BY cab.designation ASC , cname ASC ,app.date  DESC  , dat.label ASC ";
$REQUEST_223_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname, dat.label,dat.comment, DATE_FORMAT(dat.grade, '%d/%m/%Y') 
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, adm_client_arborescence as cab , adm_utilisateur as uti_man    
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (22,53,81,109,137,165,193,221,249,277) ";
$REQUEST_223_2 = " AND     app.id_eai = dat.id_eai
					AND     app.id_user = uti.id_user
					AND     uti.id_noeud=cab.id_noeud
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
					AND     uti_man.id_user=uti.id_manager
					ORDER BY cab.designation ASC , cname ASC , app.date  DESC  ";
$REQUEST_224_1 = " SELECT DATE_FORMAT(app.date, '%Y'), cab.designation, CONCAT(uti_man.nom, ' ',uti_man.prenom), CONCAT(uti.nom, ' ',uti.prenom) as cname,  dat.label, dat.comment , lis.label
					FROM eai_appraisal_data as dat, eai_appraisal as app, eai_theme as the,  eai_criterion as cri, adm_utilisateur as uti, eai_grade as gra, eai_list as lis,adm_client_arborescence as cab , adm_utilisateur as uti_man    
					WHERE   app.id_status IN (3,4,5)
					AND     the.id_theme IN (15,45,73,101,129,157,185,213,241,269) ";
$REQUEST_224_2 = " AND     app.id_eai = dat.id_eai
					AND     app.id_user = uti.id_user
                    AND     uti.id_noeud=cab.id_noeud
					AND     cri.id_theme=the.id_theme
					AND     cri.id_criterion=dat.id_criterion 
					AND     cri.id_grade=gra.id_grade
					AND     gra.id_list=lis.id_list
					AND     dat.grade=lis.value
					AND     uti_man.id_user=uti.id_manager
					ORDER BY cab.designation ASC , cname ASC ,app.date  DESC, cri.rank ASC  ";
$REQUEST_225 = " ";
$REQUEST_226 = " ";
$REQUEST_227 = " ";
$REQUEST_227 = " ";
$REQUEST_228 = " ";
$REQUEST_229 = " ";
$REQUEST_230 = " ";
/* ================================================================================================
  fonction               :   db_request_select
  description            :   fonction gérant les requêtes de type SELECT
  valeur(s) entrante(s)  :   $sql_request    = la requete SQL
  $tab_arg        = tableau des arguments attendus par la requete si nécessaire
  $_param_db_name = nom d'une base de données (si pas renseigné alors prendre la BDD centrale)
  valeur(s) sortante(s)  :   Tableau des données récupérées
  ================================================================================================ */

function db_request_select($sql_request, $tab_arg = array(), $_param_db_name = NULL, $debug = 0, $fetch_style = NULL) {

    $data = [];

    $_db_name = "";
    $_db_user = DB_USER;
    $_db_host = DB_HOST;
    $_db_port = DB_PORT;

    if ($_param_db_name != NULL) {
        $_db_name = ";dbname=" . $_param_db_name;

        if (MODE_HOST === HOST_INTERNET) {
            $_db_user = $_param_db_name; //la règle étant de donner le même nom de user à celui de la database sur l'hébergement
        }
    } else {
        $_db_name = ";dbname=" . DB_NAME;
    }
    $db_connexion = new PDO('mysql:host=' . $_db_host . ';port=' . $_db_port . $_db_name, $_db_user, DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    $req = $db_connexion->prepare($sql_request);
    $req->execute($tab_arg);

    $data = db_query_fetch($req, $fetch_style);

    $req->closeCursor();

    $db_connexion = NULL;

    return $data;
}

function db_query_fetch(PDOStatement $query, $fetch_style = NULL) {
    $data = [];
    if ($fetch_style) {
        while ($fetch = $query->fetch($fetch_style)) {
            $data[] = $fetch;
        }
    } else {
        while ($data[] = $query->fetch()) {
            
        }
    }
    return $data;
}

/* ================================================================================================
  fonction               :   db_request_delete
  description            :   fonction gérant les requêtes de type DELETE
  valeur(s) entrante(s)  :   $sql_request    = la requete SQL
  $tab_arg        = tableau des arguments attendus par la requete si nécessaire
  $_param_db_name = nom d'une base de données (si pas renseigné alors prendre la BDD centrale)
  valeur(s) sortante(s)  :   Néant
  ================================================================================================ */

function db_request_delete($sql_request, $tab_arg = array(), $_param_db_name = NULL) {

    $_db_name = "";
    $_db_user = DB_USER;
    $_db_host = DB_HOST;
    $_db_port = DB_PORT;

    if ($_param_db_name != NULL) {
        $_db_name = ";dbname=" . $_param_db_name;

        if (MODE_HOST ===
                HOST_INTERNET)
            $_db_user = $_param_db_name; //la règle étant de donner le même nom de user à celui de la database sur l'hébergement
    } else {
        $_db_name = ";dbname=" . DB_NAME;
    }
    $db_connexion = new PDO('mysql:host=' . $_db_host . ';port=' . $_db_port . $_db_name, $_db_user, DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    $req = $db_connexion->prepare($sql_request);
    $req->execute($tab_arg);

    $req->closeCursor();

    $db_connexion = NULL;
}

/* ================================================================================================
  fonction               :   db_request_update
  description            :   fonction gérant les requêtes de type UPDATE
  valeur(s) entrante(s)  :   $sql_request    = la requete SQL
  $tab_arg        = tableau des arguments attendus par la requete si nécessaire
  $_param_db_name = nom d'une base de données (si pas renseigné alors prendre la BDD centrale)
  valeur(s) sortante(s)  :   Néant
  ================================================================================================ */

function db_request_update($sql_request, $tab_arg = array(), $_param_db_name = NULL) {

    $_db_name = "";
    $_db_user = DB_USER;
    $_db_host = DB_HOST;
    $_db_port = DB_PORT;

    if ($_param_db_name != NULL) {
        $_db_name = ";dbname=" . $_param_db_name;

        if (MODE_HOST ===
                HOST_INTERNET)
            $_db_user = $_param_db_name; //la règle étant de donner le même nom de user à celui de la database sur l'hébergement
    } else {
        $_db_name = ";dbname=" . DB_NAME;
    }
    $db_connexion = new PDO('mysql:host=' . $_db_host . ';port=' . $_db_port . $_db_name, $_db_user, DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    $req = $db_connexion->prepare($sql_request);
    $req->execute($tab_arg);

    $req->closeCursor();
    $db_connexion = NULL;
}

/* ================================================================================================
  fonction               :   db_request_insert
  description            :   fonction gérant les requêtes de type INSERT
  valeur(s) entrante(s)  :   $sql_request    = la requete SQL
  $tab_arg        = tableau des arguments attendus par la requete si nécessaire
  $_param_db_name = nom d'une base de données (si pas renseigné alors prendre la BDD centrale)
  valeur(s) sortante(s)  :   Néant
  ================================================================================================ */

function db_request_insert($sql_request, $tab_arg = array(), $_param_db_name = NULL) {

    $_db_name = "";
    $_db_user = DB_USER;
    $_db_host = DB_HOST;
    $_db_port = DB_PORT;

    if ($_param_db_name != NULL) {
        $_db_name = ";dbname=" . $_param_db_name;

        if (MODE_HOST ===
                HOST_INTERNET)
            $_db_user = $_param_db_name; //la règle étant de donner le même nom de user à celui de la database sur l'hébergement
    } else {
        $_db_name = ";dbname=" . DB_NAME;
    }

    $db_connexion = new PDO('mysql:host=' . $_db_host . ';port=' . $_db_port . $_db_name, $_db_user, DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    $req = $db_connexion->prepare($sql_request);
    /* $db_connexion->beginTransaction(); */
    $req->execute($tab_arg);
    $ret = $db_connexion->lastInsertId();
    $req->closeCursor();
    /* $db_connexion->rollBack(); */
    $db_connexion = NULL;

    return $ret;
}

/* ================================================================================================
  fonction               :   db_request_create_db
  description            :   fonction gérant la création d'une base de données correspondant ici à
  la création d'un nouveau client
  valeur(s) entrante(s)  :   $_param_db_name = nom de la base de données à créer
  valeur(s) sortante(s)  :   Néant
  ================================================================================================ */

function db_request_create_db($dbName, $connection) {
    $connection->exec("CREATE SCHEMA IF NOT EXISTS $dbName

 DEFAULT CHARACTER SET utf8; USE $dbName");
}

function db_request_delete_db($_db_name_param) {
    $_db_name = ";";
    $_db_user = DB_USER;
    $_db_host = DB_HOST;
    $_db_port = DB_PORT;

    if (MODE_HOST ===
            HOST_INTERNET)
        $_db_user = $_param_db_name; //la règle étant de donner le même nom de user à celui de la database sur l'hébergement

    $sql_request = "DROP DATABASE IF EXISTS " . $_db_name_param . " ";

    $db_connexion = new PDO('mysql:host=' . $_db_host . ';port=' . $_db_port . $_db_name, $_db_user, DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    $db_connexion->exec($sql_request);

    unset($db_connexion);
}

/* ================================================================================================
  fonction               :   db_request_exec_sql_file
  description            :   fonction gérant le lancement d'un script SQL présent sur le serveur
  valeur(s) entrante(s)  :   $sql_script     = le script de la bibliothèque des scripts à lancer
  $_param_db_name = nom de la base de données à créer
  valeur(s) sortante(s)  :   Néant
  ================================================================================================ */

function GetSql($filename) {
    return str_replace([ '\n', '\r'], NULL, file_get_contents($filename));
}

function db_request_exec_sql_file($filename, $connection) {
    $connection->exec(GetSql($filename));
}
